Во второй части проекта мне нужно написать несколько SQL-запросов в Jupyter Notebook.
Необходимые данные находятся в таблицах схемы stackoverflow. Я подключусь к базе с помощью SQLAlchemy.
Некоторые задания включают дополнительные вопросы. На часть вопросов я отвечу текстом, а для некоторых создам визуализации.
import pandas as pd
import matplotlib.pyplot as plt
from sqlalchemy import create_engine
data-analyst-advanced-sql¶Эта база данных содержит схему stackoverflow, с которой мы будем работать в проекте
db_config = {
'user': 'praktikum_student', # имя пользователя
'pwd': 'Sdf4$2;d-d30pp', # пароль
'host': 'rc1b-wcoijxj3yxfsf3fs.mdb.yandexcloud.net',
'port': 6432, # порт подключения
'db': 'data-analyst-advanced-sql' # название базы данных
}
connection_string = 'postgresql://{}:{}@{}:{}/{}'.format(
db_config['user'],
db_config['pwd'],
db_config['host'],
db_config['port'],
db_config['db'],
)
Создание подключения
engine = create_engine(connection_string)
Пример запроса к базе данных
sample_df является pandas-датафреймом.
query = '''
SELECT *
FROM stackoverflow.users
LIMIT 10;
'''
sample_df = pd.read_sql_query(query, con=engine)
sample_df
| id | creation_date | display_name | last_access_date | location | reputation | views | |
|---|---|---|---|---|---|---|---|
| 0 | 1 | 2008-07-31 14:22:31 | Jeff Atwood | 2018-08-29 02:34:23 | El Cerrito, CA | 44300 | 408587 |
| 1 | 2 | 2008-07-31 14:22:31 | Geoff Dalgas | 2018-08-23 17:31:56 | Corvallis, OR | 3491 | 23966 |
| 2 | 3 | 2008-07-31 14:22:31 | Jarrod Dixon | 2018-08-30 20:56:24 | Raleigh, NC, United States | 13418 | 24396 |
| 3 | 4 | 2008-07-31 14:22:31 | Joel Spolsky | 2018-08-14 22:18:15 | New York, NY | 28768 | 73755 |
| 4 | 5 | 2008-07-31 14:22:31 | Jon Galloway | 2018-08-29 16:48:36 | San Diego, CA | 39172 | 11700 |
| 5 | 8 | 2008-07-31 21:33:24 | Eggs McLaren | 2018-04-09 02:04:56 | None | 942 | 6372 |
| 6 | 9 | 2008-07-31 21:35:27 | Kevin Dente | 2018-08-30 18:18:03 | Oakland, CA | 14337 | 4949 |
| 7 | 11 | 2008-08-01 00:59:11 | Anonymous User | 2008-08-01 00:59:11 | None | 1890 | 2123 |
| 8 | 13 | 2008-08-01 04:18:05 | Chris Jester-Young | 2018-08-30 02:47:23 | Raleigh, NC, United States | 177138 | 35414 |
| 9 | 17 | 2008-08-01 12:02:22 | Nick Berardi | 2018-01-22 01:35:38 | Issaquah, WA | 44443 | 4786 |
Выведите общую сумму просмотров постов за каждый месяц 2008 года. Если данных за какой-либо месяц в базе нет, такой месяц можно пропустить. Результат отсортируйте по убыванию общего количества просмотров.
| month_date | total_views |
|---|---|
| 2008-09-01 | 452928568 |
| 2008-10-01 | 365400138 |
| ... | ... |
# напишите запрос
query = '''
SELECT DATE_TRUNC('month', creation_date)::date as month_date,
SUM(views_count) as total_views
FROM stackoverflow.posts
GROUP BY DATE_TRUNC('month', creation_date)::date
HAVING SUM(views_count) > 0
ORDER BY total_views DESC;
'''
# выполните запрос
sample_df = pd.read_sql_query(query, con=engine)
sample_df
| month_date | total_views | |
|---|---|---|
| 0 | 2008-09-01 | 452928568 |
| 1 | 2008-10-01 | 365400138 |
| 2 | 2008-11-01 | 221759651 |
| 3 | 2008-12-01 | 197792841 |
| 4 | 2008-08-01 | 131367083 |
| 5 | 2008-07-01 | 669895 |
Проанализируйте итоговую таблицу. Отличаются ли данные за разные месяцы? С чем могут быть связаны отличия?
Данные за разные месяцы отличаются. Мы видим, что в июле количество просмотров было самым низким. После июля следующий месяц по числу минимальных просмотров — это август. Наибольшее количество просмотров было в сентябре. Так как StackOverflow — это сервис вопросов и ответов о программировании, можно предположить, что у посетителей возросла образовательная активность с наступлением осени. Прослеживается сезонность.
Выведите имена самых активных пользователей, которые в первый месяц после регистрации (включая день регистрации) дали больше 100 ответов. Вопросы, которые задавали пользователи, не учитывайте. Для каждого имени пользователя выведите количество уникальных значений user_id. Отсортируйте результат по полю с именами в лексикографическом порядке.
| display_name | count |
|---|---|
| 1800 INFORMATION | 1 |
| Adam Bellaire | 1 |
| Adam Davis | 1 |
| ... | ... |
# напишите запрос
query = '''
WITH
tests AS(
SELECT u.display_name, COUNT(p.id) AS count_posts
FROM stackoverflow.users u
INNER JOIN stackoverflow.posts p ON u.id = p.user_id
INNER JOIN stackoverflow.post_types pt ON pt.id = p.post_type_id
WHERE pt.type = 'Answer'
AND (date_trunc('day', p.creation_date) <= date_trunc('day', u.creation_date) + interval '1 month')
GROUP BY u.display_name
HAVING COUNT(p.id) > 100
)
SELECT display_name, COUNT(*) as count
FROM tests
GROUP BY display_name
ORDER BY display_name;
'''
# выполните запрос
sample_df = pd.read_sql_query(query, con=engine)
sample_df
| display_name | count | |
|---|---|---|
| 0 | 1800 INFORMATION | 1 |
| 1 | Adam Bellaire | 1 |
| 2 | Adam Davis | 1 |
| 3 | Adam Liss | 1 |
| 4 | Alan | 1 |
| ... | ... | ... |
| 74 | lomaxx | 1 |
| 75 | mattlant | 1 |
| 76 | paxdiablo | 1 |
| 77 | tvanfosson | 1 |
| 78 | tzot | 1 |
79 rows × 2 columns
Какие аномалии наблюдаются в данных? О чём они говорят?
Количество имён самых активных пользователей, которые в первый месяц после регистрации, включая день регистрации, дали больше 100 ответов, совпадает с количеством их уникальных пользовательских идентификаторов. Это говорит о том, что каждый из таких активных пользователей использовал только один аккаунт от одного user_id.
Выведите количество постов за 2008 год по месяцам. Отберите посты от пользователей, которые зарегистрировались в сентябре 2008 года и сделали хотя бы один пост в декабре того же года. Отсортируйте таблицу по значению месяца по убыванию.
| month | count |
|---|---|
| 2008-12-01 | 17641 |
| 2008-11-01 | 18294 |
| ... | ... |
# напишите запрос
query = '''
SELECT DATE_TRUNC('month', creation_date)::date as month,
COUNT(id) as count
FROM stackoverflow.posts
WHERE user_id IN (
SELECT p.user_id
FROM stackoverflow.posts p
INNER JOIN stackoverflow.users u ON p.user_id = u.id
WHERE EXTRACT(MONTH FROM p.creation_date::date) = 12
AND EXTRACT(MONTH FROM u.creation_date::date) = 9
)
GROUP BY DATE_TRUNC('month', creation_date)::date
ORDER BY month DESC;
'''
# выполните запрос
sample_df = pd.read_sql_query(query, con=engine)
sample_df
| month | count | |
|---|---|---|
| 0 | 2008-12-01 | 17641 |
| 1 | 2008-11-01 | 18294 |
| 2 | 2008-10-01 | 27171 |
| 3 | 2008-09-01 | 24870 |
| 4 | 2008-08-01 | 32 |
Изучите данные: есть ли в них аномалии? Предположите, почему могли появиться аномальные значения.
Получается несколько нелогично, что у пользователей, которые зарегистрировались в сентябре есть посты, опубликованные в августе. Возможно, дело в том, что на сервере указано время по Гринвичу UTC-0 и в момент, когда по Московскому времени UTC+3 наступил сентябрь, у этих пользователей было 3 часа на создание постов, которые «ушли в август» по времени сервера. Изучим, сколько постов создали в августе те пользователи, которые зарегистрировались в сентябре.
query = '''
SELECT u.id, u.creation_date as user_reg_date, p.creation_date as post_created
FROM stackoverflow.posts p
INNER JOIN stackoverflow.users u ON p.user_id = u.id
WHERE EXTRACT(MONTH FROM p.creation_date::date) = 8
AND EXTRACT(MONTH FROM u.creation_date::date) = 9
AND u.id IN (
SELECT p.user_id
FROM stackoverflow.posts p
INNER JOIN stackoverflow.users u ON p.user_id = u.id
WHERE EXTRACT(MONTH FROM p.creation_date::date) = 12
AND EXTRACT(MONTH FROM u.creation_date::date) = 9
)
ORDER BY u.creation_date, p.creation_date;
'''
report = pd.read_sql_query(query, con=engine)
report
| id | user_reg_date | post_created | |
|---|---|---|---|
| 0 | 31505 | 2008-09-01 00:00:00 | 2008-08-12 13:15:47 |
| 1 | 31505 | 2008-09-01 00:00:00 | 2008-08-12 14:16:42 |
| 2 | 31505 | 2008-09-01 00:00:00 | 2008-08-12 14:39:20 |
| 3 | 31505 | 2008-09-01 00:00:00 | 2008-08-12 16:32:25 |
| 4 | 31505 | 2008-09-01 00:00:00 | 2008-08-12 17:00:35 |
| 5 | 31505 | 2008-09-01 00:00:00 | 2008-08-13 14:03:39 |
| 6 | 31505 | 2008-09-01 00:00:00 | 2008-08-13 21:10:42 |
| 7 | 31505 | 2008-09-01 00:00:00 | 2008-08-14 10:28:43 |
| 8 | 31505 | 2008-09-01 00:00:00 | 2008-08-14 10:36:04 |
| 9 | 31505 | 2008-09-01 00:00:00 | 2008-08-14 11:07:01 |
| 10 | 31505 | 2008-09-01 00:00:00 | 2008-08-19 16:00:47 |
| 11 | 31505 | 2008-09-01 00:00:00 | 2008-08-19 16:19:51 |
| 12 | 31505 | 2008-09-01 00:00:00 | 2008-08-19 16:28:42 |
| 13 | 31505 | 2008-09-01 00:00:00 | 2008-08-20 20:42:05 |
| 14 | 31505 | 2008-09-01 00:00:00 | 2008-08-21 13:05:57 |
| 15 | 31505 | 2008-09-01 00:00:00 | 2008-08-22 16:38:49 |
| 16 | 31505 | 2008-09-01 00:00:00 | 2008-08-24 12:11:01 |
| 17 | 31505 | 2008-09-01 00:00:00 | 2008-08-25 08:02:43 |
| 18 | 31505 | 2008-09-01 00:00:00 | 2008-08-25 15:16:03 |
| 19 | 31505 | 2008-09-01 00:00:00 | 2008-08-25 20:16:27 |
| 20 | 31505 | 2008-09-01 00:00:00 | 2008-08-27 14:31:58 |
| 21 | 31505 | 2008-09-01 00:00:00 | 2008-08-27 14:49:01 |
| 22 | 31505 | 2008-09-01 00:00:00 | 2008-08-28 19:32:43 |
| 23 | 31505 | 2008-09-01 00:00:00 | 2008-08-29 11:37:55 |
| 24 | 31505 | 2008-09-01 00:00:00 | 2008-08-29 16:47:46 |
| 25 | 31505 | 2008-09-01 00:00:00 | 2008-08-29 17:20:35 |
| 26 | 31505 | 2008-09-01 00:00:00 | 2008-08-29 19:06:19 |
| 27 | 31505 | 2008-09-01 00:00:00 | 2008-08-29 20:16:51 |
| 28 | 31505 | 2008-09-01 00:00:00 | 2008-08-30 21:22:06 |
| 29 | 31505 | 2008-09-01 00:00:00 | 2008-08-31 14:27:32 |
| 30 | 4381 | 2008-09-03 10:48:17 | 2008-08-30 15:20:15 |
| 31 | 4381 | 2008-09-03 10:48:17 | 2008-08-30 15:27:35 |
Предположение о разных часовых поясах отбрасываем. В таблице report содержатся те 32 записи, которые были посчитаны за август. Таблица отсортирована сначала по полю с датой регистрации пользователя, затем по полю с датой создания поста. В ней всего два уникальных user_id — 31505 и 4381. Вероятно, у этих пользователей в результате какого-либо сбоя реальная дата регистрации оказалась перезаписана.
Используя данные о постах, выведите несколько полей:
Данные в таблице должны быть отсортированы по возрастанию идентификаторов пользователей, а данные об одном и том же пользователе — по возрастанию даты создания поста.
| user_id | creation_date | views_count | cumulative_count |
|---|---|---|---|
| 1 | 2008-07-31 23:41:00 | 480476 | 480476 |
| 1 | 2008-07-31 23:55:38 | 136033 | 616509 |
| 1 | 2008-07-31 23:56:41 | 0 | 616509 |
| ... | ... | ... | ... |
| 2 | 2008-07-31 23:56:41 | 79087 | 79087 |
| 2 | 2008-08-01 05:09:56 | 65443 | 144530 |
| ... | ... | ... | ... |
# напишите запрос
query = '''
SELECT user_id, creation_date, views_count,
SUM(views_count) OVER(PARTITION BY user_id ORDER BY creation_date) as cumulative_count
FROM stackoverflow.posts
ORDER BY user_id, creation_date
'''
# выполните запрос
sample_df = pd.read_sql_query(query, con=engine)
sample_df
| user_id | creation_date | views_count | cumulative_count | |
|---|---|---|---|---|
| 0 | 1 | 2008-07-31 23:41:00 | 480476 | 480476 |
| 1 | 1 | 2008-07-31 23:55:38 | 136033 | 616509 |
| 2 | 1 | 2008-07-31 23:56:41 | 0 | 616509 |
| 3 | 1 | 2008-08-04 02:45:08 | 0 | 616509 |
| 4 | 1 | 2008-08-04 04:31:03 | 0 | 616509 |
| ... | ... | ... | ... | ... |
| 243791 | 5696608 | 2008-12-23 16:00:37 | 0 | 2804 |
| 243792 | 5696608 | 2008-12-23 17:35:09 | 0 | 2804 |
| 243793 | 5696608 | 2008-12-24 01:02:48 | 0 | 2804 |
| 243794 | 5696608 | 2008-12-30 14:34:45 | 0 | 2804 |
| 243795 | 5696608 | 2008-12-30 16:32:12 | 0 | 2804 |
243796 rows × 4 columns
Найдите среднее количество постов пользователей в день за август 2008 года. Отберите данные о пользователях, которые опубликовали больше 120 постов за август. Дни без публикаций не учитывайте.
Отсортируйте результат по возрастанию среднего количества постов. Значения можно не округлять.
| user_id | avg_daily |
|---|---|
| 116 | 4.777778 |
| 234 | 5.208333 |
| ... | ... |
# напишите запрос
query = '''
WITH august AS (
SELECT user_id, creation_date::date AS date, COUNT(id) AS count
FROM stackoverflow.posts
WHERE user_id IN
(SELECT user_id
FROM stackoverflow.posts
WHERE EXTRACT(MONTH FROM creation_date::date) = 8
GROUP BY user_id
HAVING COUNT(id) > 120)
AND EXTRACT(MONTH FROM creation_date::date) = 8
GROUP BY user_id, creation_date::date
ORDER BY user_id, date
)
SELECT user_id, AVG(count) as avg_daily
FROM august
WHERE count > 0
GROUP BY user_id
ORDER BY avg_daily;
'''
# выполните запрос
sample_df = pd.read_sql_query(query, con=engine)
sample_df
| user_id | avg_daily | |
|---|---|---|
| 0 | 116 | 4.777778 |
| 1 | 234 | 5.208333 |
| 2 | 91 | 5.681818 |
| 3 | 905 | 7.000000 |
| 4 | 383 | 7.277778 |
Сколько в среднем дней в период с 1 по 7 декабря 2008 года пользователи взаимодействовали с платформой? Для каждого пользователя отберите дни, в которые он или она опубликовали хотя бы один пост. Нужно получить одно целое число — не забудьте округлить результат.
| result |
|---|
| <целое число> |
# напишите запрос
query = '''
WITH ranks AS
(SELECT f.user_id, MAX(f.rn) as days
FROM
(SELECT user_id, creation_date::date,
DENSE_RANK() OVER(PARTITION BY user_id ORDER BY creation_date::date) as rn
FROM stackoverflow.posts
WHERE EXTRACT(DAY FROM creation_date::date) IN (1, 2, 3, 4, 5, 6, 7)
AND EXTRACT(MONTH FROM creation_date::date) = 12) AS f
GROUP BY user_id)
SELECT ROUND(AVG(days))::integer
FROM ranks;
'''
# выполните запрос
sample_df = pd.read_sql_query(query, con=engine)
sample_df
| round | |
|---|---|
| 0 | 2 |
Проанализируйте итоговую таблицу — какие выводы можно сделать?
Среднее значение очень неустойчиво к выбросам в данных. Число 2 может означать то, что с 1 по 7 декабря многие пользователи практически не совершали взаимодействие с платформой. Построим гистограмму для того, чтобы оценить распределение числа дней, которые провели пользователи на платформе в рассматриваемый период.
query = '''
SELECT f.user_id, MAX(f.rn) as days
FROM
(SELECT user_id, creation_date::date,
DENSE_RANK() OVER(PARTITION BY user_id ORDER BY creation_date::date) as rn
FROM stackoverflow.posts
WHERE EXTRACT(DAY FROM creation_date::date) IN (1, 2, 3, 4, 5, 6, 7)
AND EXTRACT(MONTH FROM creation_date::date) = 12) AS f
GROUP BY user_id
'''
report = pd.read_sql_query(query, con=engine)
report.hist('days', bins=7, figsize=(11, 5))
plt.title('Распределение количества дней пользоввательского взаимодействия с платформой')
plt.xlabel('Дни')
plt.ylabel('Взаимодействия')
plt.show()
Более, чем 2000 пользователей платформы в период с 1 по 7 декабря 2008 года совершали с ней взаимодействие в течение одного дня. Мы видим, что распределение убывает по экспоненте — большему числу дней взаимодействия соответствует меньшее число пользователей. Это объясняет, почему среднее значение равно двум дням.
Выведите историю активности каждого пользователя в таком виде: идентификатор пользователя, дата публикации поста. Отсортируйте вывод по возрастанию идентификаторов пользователей, а для каждого пользователя — по возрастанию даты публикации.
Добавьте в таблицу новое поле: для каждого поста в нём будет указано название месяца предпоследней публикации пользователя относительно текущей. Если такой публикации нет, укажите NULL. Python автоматически поменяет NULL на None, но дополнительно преобразовывать значения None вам не нужно.
Посмотрите внимательно на образец таблицы: для первых двух постов предпоследней публикации нет, но, начиная с третьего поста, в новое поле входит нужный месяц. Для следующего пользователя в первые две записи поля second_last_month тоже войдёт NULL.
| user_id | creation_date | second_last_month |
|---|---|---|
| 1 | 2008-07-31 23:41:00 | None |
| 1 | 2008-07-31 23:55:38 | None |
| 1 | 2008-07-31 23:56:41 | July |
| 1 | 2008-08-04 02:45:08 | July |
| 1 | 2008-08-04 04:31:03 | July |
| 1 | 2008-08-04 08:04:42 | August |
| ... | ... | ... |
# напишите запрос
query = '''
WITH temporary AS
(SELECT user_id, creation_date,
CASE
WHEN month_number = 1 THEN 'January'
WHEN month_number = 2 THEN 'February'
WHEN month_number = 3 THEN 'March'
WHEN month_number = 4 THEN 'April'
WHEN month_number = 5 THEN 'May'
WHEN month_number = 6 THEN 'June'
WHEN month_number = 7 THEN 'July'
WHEN month_number = 8 THEN 'August'
WHEN month_number = 9 THEN 'September'
WHEN month_number = 10 THEN 'October'
WHEN month_number = 11 THEN 'November'
WHEN month_number = 12 THEN 'December'
END AS month
FROM
(SELECT user_id, creation_date, EXTRACT(MONTH FROM creation_date::date)::integer as month_number
FROM stackoverflow.posts) AS sub
ORDER BY user_id, creation_date)
SELECT user_id, creation_date,
LAG(month, 2, NULL) OVER(PARTITION BY user_id ORDER BY creation_date) AS second_last_month
FROM temporary
'''
# выполните запрос
sample_df = pd.read_sql_query(query, con=engine)
sample_df
| user_id | creation_date | second_last_month | |
|---|---|---|---|
| 0 | 1 | 2008-07-31 23:41:00 | None |
| 1 | 1 | 2008-07-31 23:55:38 | None |
| 2 | 1 | 2008-07-31 23:56:41 | July |
| 3 | 1 | 2008-08-04 02:45:08 | July |
| 4 | 1 | 2008-08-04 04:31:03 | July |
| ... | ... | ... | ... |
| 243791 | 5696608 | 2008-12-23 16:00:37 | December |
| 243792 | 5696608 | 2008-12-23 17:35:09 | December |
| 243793 | 5696608 | 2008-12-24 01:02:48 | December |
| 243794 | 5696608 | 2008-12-30 14:34:45 | December |
| 243795 | 5696608 | 2008-12-30 16:32:12 | December |
243796 rows × 3 columns
Рассчитайте аналог Retention Rate по месяцам для пользователей StackOverflow. Объедините пользователей в когорты по месяцу их первого поста. Возвращение определяйте по наличию поста в текущем месяце.
| cohort_dt | session_date | users_cnt | cohort_users_cnt | retention_rate |
|---|---|---|---|---|
| 2008-07-01 00:00:00 | 2008-07-01 00:00:00 | 3 | 3 | 100 |
| 2008-07-01 00:00:00 | 2008-08-01 00:00:00 | 2 | 3 | 66,67 |
| 2008-07-01 00:00:00 | 2008-09-01 00:00:00 | 1 | 3 | 33,33 |
| 2008-07-01 00:00:00 | 2008-10-01 00:00:00 | 2 | 3 | 66,67 |
| 2008-07-01 00:00:00 | 2008-11-01 00:00:00 | 1 | 3 | 33,33 |
| 2008-07-01 00:00:00 | 2008-12-01 00:00:00 | 2 | 3 | 66,67 |
| 2008-08-01 00:00:00 | 2008-08-01 00:00:00 | 2151 | 2151 | 100 |
| ... | ... | ... | ... | ... |
# напишите запрос
query = '''
WITH profiles AS
(SELECT DISTINCT DATE_TRUNC('month', creation_date::date)::date AS cohort_dt,
user_id,
COUNT(user_id) OVER(PARTITION BY DATE_TRUNC('month', creation_date::date)) AS cohort_users_cnt
FROM
(SELECT *,
DENSE_RANK() OVER(PARTITION BY user_id ORDER BY creation_date) as rn
FROM stackoverflow.posts
ORDER BY user_id) as subq
WHERE rn = 1),
sessions AS
(SELECT user_id,
DATE_TRUNC('month', creation_date::date)::date AS session_date
FROM stackoverflow.posts
GROUP BY 1, 2)
SELECT p.cohort_dt,
session_date,
COUNT(p.user_id) AS users_cnt,
cohort_users_cnt,
ROUND(COUNT(p.user_id) * 100.0 / cohort_users_cnt, 2) AS retention_rate
FROM profiles p JOIN sessions s ON p.user_id = s.user_id
GROUP BY 1, 2, 4
ORDER BY 1, 2;
'''
# выполните запрос
sample_df = pd.read_sql_query(query, con=engine)
sample_df.head(10)
| cohort_dt | session_date | users_cnt | cohort_users_cnt | retention_rate | |
|---|---|---|---|---|---|
| 0 | 2008-07-01 | 2008-07-01 | 3 | 3 | 100.00 |
| 1 | 2008-07-01 | 2008-08-01 | 2 | 3 | 66.67 |
| 2 | 2008-07-01 | 2008-09-01 | 1 | 3 | 33.33 |
| 3 | 2008-07-01 | 2008-10-01 | 2 | 3 | 66.67 |
| 4 | 2008-07-01 | 2008-11-01 | 1 | 3 | 33.33 |
| 5 | 2008-07-01 | 2008-12-01 | 2 | 3 | 66.67 |
| 6 | 2008-08-01 | 2008-08-01 | 2151 | 2151 | 100.00 |
| 7 | 2008-08-01 | 2008-09-01 | 1571 | 2151 | 73.04 |
| 8 | 2008-08-01 | 2008-10-01 | 1275 | 2151 | 59.27 |
| 9 | 2008-08-01 | 2008-11-01 | 1050 | 2151 | 48.81 |
Постройте тепловую карту Retention Rate. Какие аномалии или другие необычные явления удалось выявить? Сформулируйте гипотезы о возможных причинах.
# постройте тепловую карту Retention Rate
# Чтобы построить тепловую карту Retention Rate, необходим расчёт лайфтайма. Посчитаем его с помощью sql, поправив
# запрос и по новым данным будем строить сводную таблицу.
query = '''
WITH profiles AS
(SELECT DISTINCT DATE_TRUNC('month', creation_date::date)::date AS cohort_dt,
user_id,
COUNT(user_id) OVER(PARTITION BY DATE_TRUNC('month', creation_date::date)) AS cohort_users_cnt
FROM
(SELECT *,
DENSE_RANK() OVER(PARTITION BY user_id ORDER BY creation_date) as rn
FROM stackoverflow.posts
ORDER BY user_id) as subq
WHERE rn = 1),
sessions AS
(SELECT user_id,
DATE_TRUNC('month', creation_date::date)::date AS session_date
FROM stackoverflow.posts
GROUP BY 1, 2)
SELECT p.cohort_dt,
session_date,
COUNT(p.user_id) AS users_cnt,
cohort_users_cnt,
-- далее вместо умножения на 100.0, будет умножение на 1.0, чтобы в хитмэпе получить проценты
ROUND(COUNT(p.user_id) * 1.0 / cohort_users_cnt, 4) AS retention_rate, -- а точность сохраним до 4 знаков
(EXTRACT(MONTH FROM session_date::date) - EXTRACT(MONTH FROM p.cohort_dt::date))::integer AS lifetime
FROM profiles p JOIN sessions s ON p.user_id = s.user_id
GROUP BY 1, 2, 4
ORDER BY 1, 2;
'''
report = pd.read_sql_query(query, con=engine)
# Убедимся, что столбец lifetime получился
report.head(10)
| cohort_dt | session_date | users_cnt | cohort_users_cnt | retention_rate | lifetime | |
|---|---|---|---|---|---|---|
| 0 | 2008-07-01 | 2008-07-01 | 3 | 3 | 1.0000 | 0 |
| 1 | 2008-07-01 | 2008-08-01 | 2 | 3 | 0.6667 | 1 |
| 2 | 2008-07-01 | 2008-09-01 | 1 | 3 | 0.3333 | 2 |
| 3 | 2008-07-01 | 2008-10-01 | 2 | 3 | 0.6667 | 3 |
| 4 | 2008-07-01 | 2008-11-01 | 1 | 3 | 0.3333 | 4 |
| 5 | 2008-07-01 | 2008-12-01 | 2 | 3 | 0.6667 | 5 |
| 6 | 2008-08-01 | 2008-08-01 | 2151 | 2151 | 1.0000 | 0 |
| 7 | 2008-08-01 | 2008-09-01 | 1571 | 2151 | 0.7304 | 1 |
| 8 | 2008-08-01 | 2008-10-01 | 1275 | 2151 | 0.5927 | 2 |
| 9 | 2008-08-01 | 2008-11-01 | 1050 | 2151 | 0.4881 | 3 |
# строим треугольную таблицу
report = report.pivot_table(index='cohort_dt', columns='lifetime', values='retention_rate').fillna(0)
report
| lifetime | 0 | 1 | 2 | 3 | 4 | 5 |
|---|---|---|---|---|---|---|
| cohort_dt | ||||||
| 2008-07-01 | 1.0 | 0.6667 | 0.3333 | 0.6667 | 0.3333 | 0.6667 |
| 2008-08-01 | 1.0 | 0.7304 | 0.5927 | 0.4881 | 0.4156 | 0.0000 |
| 2008-09-01 | 1.0 | 0.5382 | 0.3863 | 0.3256 | 0.0000 | 0.0000 |
| 2008-10-01 | 1.0 | 0.4519 | 0.3365 | 0.0000 | 0.0000 | 0.0000 |
| 2008-11-01 | 1.0 | 0.4036 | 0.0000 | 0.0000 | 0.0000 | 0.0000 |
| 2008-12-01 | 1.0 | 0.0000 | 0.0000 | 0.0000 | 0.0000 | 0.0000 |
import seaborn as sns
plt.figure(figsize=(15, 6)) # задаём размер графика
sns.heatmap(
report.drop(columns=0), # удаляем лишние столбцы
annot=True, # включаем подписи
fmt='.2%', # переводим значения в проценты
)
plt.title('Тепловая карта удержания') # название графика
plt.show()
# опишите аномалии или другие необычные явления и сформулируйте гипотезы
На тепловой карте видно, что декабрьская когорта не успела прожить достаточное количество месяцев, поэтому сказать о ней мы ничего не можем, для ноябрьской у нас есть данные только для первого лайфтайма. Можно выделить хорошие показатели удержания для когорты августа — практически по всем лайфтаймам она показала лучшие результаты. Это напрямую говорит и о качестве привлечённых пользователей и о том, что первый лайфтайм этой когорты попал на сентябрь. Ранее мы уже выдвигали гипотезу о том, что с наступлением осени образовательная активность повышается. Когорта июля малочисленна: в неё входит всего 3 пользователя и на графике видно, что на первый лайфтайм из этой когорты вернулись 2 пользователя, на второй — 1, на третий снова 2, на четвёртый снова 1 и на пятый — опять 2. Показатели удержания выглядят несколько неестественно как раз по той причине, что в когорту входят всего 3 пользователя.
На сколько процентов менялось количество постов ежемесячно с 1 сентября по 31 декабря 2008 года? Отобразите таблицу со следующими полями:
Если постов стало меньше, значение процента должно быть отрицательным, если больше — положительным. Округлите значение процента до двух знаков после запятой.
Напомним, что при делении одного целого числа на другое в PostgreSQL в результате получится целое число, округлённое до ближайшего целого вниз. Чтобы этого избежать, переведите делимое в тип numeric.
| creation_month | posts_count | percentage |
|---|---|---|
| 9 | 70731 | Nan |
| 10 | 63102 | -10.33 |
| ... | ... | ... |
# напишите запрос
query = '''
WITH aga AS
(SELECT EXTRACT(MONTH FROM creation_date::date)::integer as creation_month,
COUNT(id) as posts_count
FROM stackoverflow.posts
WHERE EXTRACT(MONTH FROM creation_date::date)::integer >= 9
GROUP BY 1)
SELECT creation_month, posts_count,
ROUND((posts_count::numeric / (LAG(posts_count) OVER(ORDER BY creation_month)) - 1) * 100, 2) as percentage
FROM aga
'''
# выполните запрос
sample_df = pd.read_sql_query(query, con=engine)
sample_df.head(10)
| creation_month | posts_count | percentage | |
|---|---|---|---|
| 0 | 9 | 70371 | NaN |
| 1 | 10 | 63102 | -10.33 |
| 2 | 11 | 46975 | -25.56 |
| 3 | 12 | 44592 | -5.07 |
Постройте круговую диаграмму с количеством постов по месяцам.
# постройте круговую диаграмму с количеством постов по месяцам
import plotly.express as px
import warnings
warnings.simplefilter("ignore")
report = sample_df[['posts_count']]
report['month'] = ['September', 'October', 'November', 'December']
fig = px.pie(report, values='posts_count', names='month', title='Количество постов по месяцам',
labels={'month':'месяц', 'posts_count':'количество постов'})
fig.show()
Выгрузите данные активности пользователя, который опубликовал больше всего постов за всё время. Выведите данные за октябрь 2008 года в таком виде:
| week_creation | creation_date |
|---|---|
| 40 | 2008-10-05 09:00:58 |
| 41 | 2008-10-12 21:22:23 |
| ... | ... |
# напишите запрос
query = '''
WITH weeks_and_dates AS
(SELECT EXTRACT(WEEK FROM creation_date::date)::integer AS week_creation,
creation_date
FROM stackoverflow.posts
WHERE user_id =
(SELECT user_id
FROM stackoverflow.posts
GROUP BY user_id
ORDER BY COUNT(id) DESC
LIMIT 1)
AND EXTRACT(MONTH FROM creation_date::date)=10)
SELECT week_creation, MAX(creation_date)
FROM weeks_and_dates
GROUP BY week_creation
ORDER BY week_creation;
'''
# выполните запрос
sample_df = pd.read_sql_query(query, con=engine)
sample_df.head(50)
| week_creation | max | |
|---|---|---|
| 0 | 40 | 2008-10-05 09:00:58 |
| 1 | 41 | 2008-10-12 21:22:23 |
| 2 | 42 | 2008-10-19 06:49:30 |
| 3 | 43 | 2008-10-26 21:44:36 |
| 4 | 44 | 2008-10-31 22:16:01 |